/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.AdlZhsq;
import me.zhengjie.business.domain.Jzdj;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://el-admin.vip
* @author xiahongbo
* @date 2022-05-30
**/
public interface AdlZhsqRepository extends JpaRepository<AdlZhsq, String>, JpaSpecificationExecutor<AdlZhsq> {

    /**
     * 根据自评结果查询总分
     * @param ids /
     * @return /
     */
    @Query(value = "select sum(t.bis_adl_xmxx_fs) score from BIS_ADL_XMXX t where t.bis_adl_xmxx_id in (?1)",nativeQuery = true)
    String queryScore(String[] ids);

    @Query(value = "select sum(t.bis_adl_xmxx_fs) from bis_adl_zhsq  t where " +
            "t.bis_zhsq_id = ?1 and " +
            "t.ywlx = 'RYLX02' and " +
            "t.bis_adl_xm_id in (" +
            "'402883ba943fefb7019444d56d4b0003'," +
            "'402883ba943fefb7019444d87857000d'," +
            "'402883ba943fefb7019444d98e330013'," +
            "'402883ba943fefb7019444db24650018'," +
            "'402883ba943fefb7019444dc2c91001e'," +
            "'402883ba943fefb7019444dd34e60024'," +
            "'402883ba943fefb7019444dea7e1002b'," +
            "'402883ba943fefb7019444e1f9e50034'," +
            "'402883ba943fefb7019444e311cb003b'," +
            "'402883ba943fefb7019444e42fd50040')",nativeQuery = true)
    String queryC1(Long zhid);

    @Query(value = "select sum(t.bis_adl_xmxx_fs) from bis_adl_zhsq  t where " +
            "t.bis_zhsq_id = ?1 and " +
            "t.ywlx = 'RYLX02' and " +
            "t.bis_adl_xm_id in (" +
            "'402883ba943fefb7019444e4c9e20043'," +
            "'402883ba943fefb7019444e5fe520049'," +
            "'402883ba943fefb7019444e7063c004f'," +
            "'402883ba943fefb7019444e7f8b40055')",nativeQuery = true)
    String queryC2(Long zhid);

    @Query(value = "select sum(t.bis_adl_xmxx_fs) from bis_adl_zhsq  t where " +
            "t.bis_zhsq_id = ?1 and " +
            "t.ywlx = 'RYLX02' and " +
            "t.bis_adl_xm_id in (" +
            "'402883ba943fefb7019444e960de005b'," +
            "'402883ba943fefb7019444ea8d5b0061'," +
            "'402883ba943fefb7019444eb7c660067')",nativeQuery = true)
    String queryC3(Long zhid);

    List<AdlZhsq> findByBisAdlZhsqIdAndBisUserIdAndBisZfbsNull(Long bisAdlZhsqId,Long bisUserId) ;

    /**
     * 查询评估人员的adl量表结果和视频地址
     * @param zhid /
     * @return /
     */
    @Query(value = "select t1.bis_adl_xmxx_id, t2.path " +
            "  from BIS_ADL_ZHSQ t1, TOOL_LOCAL_STORAGE t2 " +
            " where t1.bis_adl_zhsq_id = t2.ywid " +
            "   and t1.bis_zhsq_id = ?1 " +
            "   and t1.ywlx = 'RYLX02' " +
            "   and t1.BIS_ZFBS is null ",nativeQuery = true)
    List<Object> queryPgAdl(Long zhid);

    /**
     * 查询评估人员的adl量表结果和视频地址
     * @param zhid /
     * @return /
     */
    @Query(value = "select t1.bis_adl_xmxx_id " +
            "  from BIS_ADL_ZHSQ t1 " +
            " where t1.bis_zhsq_id = ?1 " +
            "   and t1.ywlx = 'RYLX02' " +
            "   and t1.BIS_ZFBS is null ",nativeQuery = true)
    List<Object> queryJgPgAdl(Long zhid);

    /**
     * 作废ADL
     * @param id 照护主键
     */
    @Modifying
    @Query(value = "update bis_adl_zhsq set bis_zfbs = '1' where bis_zhsq_id = ?1",nativeQuery = true)
    void updateBisZfbs(Long id);

    /**
     * 删除ADL
     * @param id 照护主键
     */
    @Modifying
    @Query(value = "delete from bis_adl_zhsq  where bis_zhsq_id = ?1 and ywlx is null",nativeQuery = true)
    void deleteAdlByZhid(Long id);
}